﻿--|--------------------------------------------------------------------------------
--| [GetPersonList] - Insert Procedure Script for Game
--|--------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id (N'[dbo].[GetPersonList]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) 
  DROP PROCEDURE [dbo].[GetPersonList]
GO

CREATE PROCEDURE [dbo].[GetPersonList]
(
	@TeamID int
)
AS
	SET NOCOUNT ON

SELECT E.ID, 
	E.Name, 
	E.Description, 
	E.TeamID, 
	E.Photo, 
	E.EntityTypeID, 
	ET.Name as EntityType,
	E.MaxSendingPause,
	EA1.Value as FullName,
	CONVERT(datetime, EA2.Value) as BirthDate,
	EA3.Value as TeamPhone,
	EA4.Value as OtherPhones,
	EA5.Value as ICQ,
	EA6.Value as Skype,
	EA7.Value as OtherIMs,
	EA8.Value as Radio,
	EA9.Value as Tracker,
	EA10.Value as OtherDevices
FROM dbo.Entity E
	INNER JOIN EntityType ET ON ET.ID = E.EntityTypeID
	LEFT JOIN dbo.EntityAttribute EA1 ON E.ID = EA1.EntityID AND EA1.EntityAttributeTypeID = 'FNAME'
	LEFT JOIN dbo.EntityAttribute EA2 ON E.ID = EA2.EntityID AND EA2.EntityAttributeTypeID = 'BIRTH'
	LEFT JOIN dbo.EntityAttribute EA3 ON E.ID = EA3.EntityID AND EA3.EntityAttributeTypeID = 'TPHONE'
	LEFT JOIN dbo.EntityAttribute EA4 ON E.ID = EA4.EntityID AND EA4.EntityAttributeTypeID = 'OPHONES'
	LEFT JOIN dbo.EntityAttribute EA5 ON E.ID = EA5.EntityID AND EA5.EntityAttributeTypeID = 'ICQ'
	LEFT JOIN dbo.EntityAttribute EA6 ON E.ID = EA6.EntityID AND EA6.EntityAttributeTypeID = 'SKYPE'
	LEFT JOIN dbo.EntityAttribute EA7 ON E.ID = EA7.EntityID AND EA7.EntityAttributeTypeID = 'IM'
	LEFT JOIN dbo.EntityAttribute EA8 ON E.ID = EA8.EntityID AND EA8.EntityAttributeTypeID = 'TRAN'
	LEFT JOIN dbo.EntityAttribute EA9 ON E.ID = EA9.EntityID AND EA9.EntityAttributeTypeID = 'TRACK'
	LEFT JOIN dbo.EntityAttribute EA10 ON E.ID = EA10.EntityID AND EA10.EntityAttributeTypeID = 'DEVS'
WHERE E.EntityTypeID = 1 -- Человек
AND		E.TeamID = IsNull(@TeamID,E.TeamID)
	
GO
 


 